package com.hsnn.medstgmini.dao.impl;

import java.math.BigInteger;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.util.DbUtil;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category Hibernate 查询通用类
 * @author 
 * @date 2015-06-14
 */
@Repository
public class HibernateSqlUtilImpl implements HibernateSqlUtil {
	
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	private Session session = null;
	private Session getSession(){
		if (null == session){
			session = sessionFactory.openSession();
		}
		return session;
		
	}
	
	/**
	 * 
	 *@category 根据对象新增一条记录
	 *@author 邱磊
	 *@date 2015年6月15日 上午11:26:03
	 *@param obj
	 *@return
	 */
	public boolean add(Object obj){
		try {
			getSession().save(obj);
			getSession().flush();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
	}
	/**
	 * 
	 *@category 根据对象删除一条记录
	 *@author 邱磊
	 *@date 2015年6月15日 上午11:32:02
	 *@param obj
	 *@return
	 */
	public boolean delete(Object obj){
		try {
			getSession().delete(obj);
			getSession().flush();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
	}
	public boolean delete(String entyName,Object obj){
		try {
			getSession().delete(entyName, obj);;
			getSession().flush();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
	}
	/**
	 * 
	 *@category 根据对象更新一条记录
	 *@author 邱磊
	 *@date 2015年6月15日 上午11:32:42
	 *@param obj
	 *@return
	 */
	public boolean update(Object obj){
		try {
			getSession().update(obj);
			getSession().flush();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}
	}
	
	/**
	 * 
	 *@category 根据实体对象与Id获取信息
	 *@author 邱磊
	 *@date 2015年6月15日 上午11:19:03
	 *@param obj
	 *@param id
	 *@return
	 */
	public Object getUniquerResult(Class<?> obj,String id){
		return getSession().get( obj, id);
	}
	/**
	 * 
	 *@category 根据hql语句获取单条记录
	 *@author 邱磊
	 *@date 2015年6月15日 上午11:19:38
	 *@param sql
	 *@return
	 */
	public Object getUniqueResult( String sql) {
		return getSession().createQuery(sql).setCacheable(true).uniqueResult();
	}

	/*
	 * @category 通过SQL语句与ParamList从数据库中取出单条记录.
	 * @param session, hibernate session
	 * @param sql SQL语句
	 * @param param 输入参数，取代SQL中的？
	 * @author 
	 * @date 2015-06-14
	 */
	public Object getUniqueResult(String sql, List<String> param) {
		Query query = getSession().createQuery(sql).setCacheable(true);
		if (param != null) {
			for (int i = 0; i < param.size(); i++) {
				query.setParameter(i, param.get(i));
			}
		}
		Object obj = query.uniqueResult();
		return obj;
	}

	/*
	 * @category 通过SQL语句从数据库中取出单条记录.
	 * @param session, hibernate session
	 * @param sql SQL语句
	 * @author 
	 * @date 2015-06-14
	 */
	public Object getFirstResult(String sql) {		
		return getSession().createQuery(sql).setCacheable(true).setMaxResults(1).uniqueResult();
	}

	/*
	 * @category 通过SQL语句与ParamList从数据库中取出单条记录.
	 * @param session, hibernate session
	 * @param sql SQL语句
	 * @param param 输入参数，取代SQL中的？
	 * @author 
	 * @date 2015-06-14
	 */
	public Object getFirstResult(String sql, List<String> param) {
		Query query = getSession().createQuery(sql).setCacheable(true);
		
		if (param != null) {
			for (int i = 0; i < param.size(); i++) {
				query.setParameter(i, param.get(i));
			}
		}
		
		Object obj = query.setMaxResults(1).uniqueResult();
		return obj;
	}

	/*
	 * @category 通过SQL语句从数据库中取出全部记录.
	 * @param session, hibernate session
	 * @param sql SQL语句
	 * @param param 输入参数，取代SQL中的？
	 * @author 
	 * @date 2015-06-14
	 */
	public List<?> getList( String sql, List<String> param) {

		Query query = getSession().createQuery(sql).setCacheable(true);
		if (param != null) {
			for (int i = 0; i < param.size(); i++) {
				query.setParameter(i, param.get(i));
			}
		}
		List<?> list = query.list();
		return list;
	}

	/*
	 * @category 通过SQL语句从数据库中取出全部记录.
	 * @param session, hibernate session
	 * @param sql SQL语句
	 * @param param 输入参数，取代SQL中的占位符
	 * @author 
	 * @date 2015-06-14
	 */
	@SuppressWarnings("unchecked")
	public List<?> getList(String sql, Map<String, ?> params) {

		Query query = getSession().createQuery(sql).setCacheable(true);
		
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}

		List<?> list = query.list();
		return list;
	}
	
	/*
	 * @category 通过SQL语句从数据库中取出定长记录.
	 * @param session, hibernate session
	 * @param begIndex 第一条记录
	 * @param count 记录总条数
	 * @param sql SQL语句
	 * @author 
	 * @date 2015-06-14
	 */
	public List<?> getList(int begIndex, int count, String sql) {

		Query query = getSession().createQuery(sql).setCacheable(true);
		query.setFirstResult(begIndex - 1);
		query.setMaxResults(count);
		List<?> list = query.list();
		return list;
	}

	/*
	 * @category 通过SQL与ParamList从数据库中取出定长记录.
	 * @param session, hibernate session
	 * @param sql SQL语句
	 * @param begIndex 第一条记录
	 * @param count 记录总条数
     * @param param 输入参数，取代SQL中的占位符
	 * @author 
	 * @date 2015-06-14
	 */
	@SuppressWarnings("unchecked")
	public Pagination getPageList(String sql, Map<String, ?> params, Pagination page) {
		sql = DbUtil.resetSql(sql);
		Query query = getSession().createQuery(sql).setCacheable(true);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		if (page.getMaxResults() > 0) {
				String countSql = sql;
				int beginIndex;
				int endIndex;
				if (countSql.indexOf("SELECT") == -1) {
					countSql = "SELECT COUNT(*) " + countSql;
				} else {
					beginIndex = countSql.indexOf("FROM");
					countSql = "SELECT COUNT(*) "
							+ countSql.substring(beginIndex);
				}
				endIndex = countSql.indexOf("ORDER BY");
				if (endIndex > 0) {
					countSql = countSql.substring(0, endIndex);
				}
				Query countQuery = getSession().createQuery(countSql);
				countQuery.setCacheable(true);
				if (params != null) {
					Iterator<?> list = params.entrySet().iterator();
					while (list.hasNext()) {
						Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
						query.setParameter((String) entry.getKey(), entry.getValue());
					}
				}
				Long records=(Long) countQuery.uniqueResult();
				page.setRecords(records);
			}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
			
		List<?> list = query.list();
		page.setRows(list);
		return page;
		
	}

	/*
	 * @category 通过SQL与ParamList从数据库中取出定长记录.
	 * @param session, hibernate session
	 * @param sql SQL语句
	 * @param begIndex 第一条记录
	 * @param count 记录总条数
     * @param param 输入参数，取代SQL中的"?"
	 * @author 
	 * @date 2015-06-14
	 */
	public Pagination getPageList(String sql, List<String> param, Pagination page) {
		
		Query query = getSession().createQuery(sql).setCacheable(true);
		if (param != null) {
			for (int i = 0; i < param.size(); i++) {
				query.setParameter(i, param.get(i));
			}
		}
		
		
		if (page.getMaxResults() > 0) {
			String countSql = sql;
			int beginIndex;
			int endIndex;
			if (countSql.indexOf("SELECT") == -1) {
				countSql = "SELECT COUNT(*) " + countSql;
			} else {
				beginIndex = countSql.indexOf("FROM");
				countSql = "SELECT COUNT(*) "
						+ countSql.substring(beginIndex);
			}
			endIndex = countSql.indexOf("ORDER BY");
			if (endIndex > 0) {
				countSql = countSql.substring(0, endIndex);
			}
			Query countQuery = getSession().createQuery(countSql);
			countQuery.setCacheable(true);
			if (param != null) {
				for (int i = 0; i < param.size(); i++) {
					query.setParameter(i, param.get(i));
				}
			}
			Long records=(Long) countQuery.uniqueResult();
			page.setRecords(records);
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		List<?> list = query.list();
		page.setRows(list);
		return page;
		
	}
	
/************************************************以下以SQL执行**********************************************************/
	/**
	 * 
	 *@category 根据参数，查询sql所有的记录（原生SQL）
	 *@author 邱磊
	 *@date 2015年6月15日 上午11:38:40
	 *@param sql
	 *@param param 
	 *@return
	 */
	public List<?> getSqlList (String sql, List<String> param){
		Query query = getSession().createSQLQuery(sql);
		if (param != null) {
			for (int i = 0; i < param.size(); i++) {
				query.setParameter(i, param.get(i));
			}
		}
		List<?> list = query.list();
		return list;
	}
	/**
	 * 
	 *@category 根据参数，查询sql所有的记录（原生SQL）
	 *@author 邱磊
	 *@date 2015年6月15日 上午11:38:40
	 *@param sql
	 *@param param 
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public List<?> getSqlList(String sql, Map<String, ?> params){
		Query query = getSession().createSQLQuery(sql).setCacheable(true);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		List<?> list = query.list();
		return list;
	}
	
	/**
	 *@category 根据条件原生SQL分页查询
	 *@author 邱磊
	 *@date 2015年6月15日 上午11:43:18
	 *@param sql
	 *@param params
	 *@param page
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getSqlPageList(String sql, Map<String, ?> params, Pagination page){
		Query query = getSession().createSQLQuery(sql);
		
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		if (page.getMaxResults() > 0) {
			String countSql = "select count(*) from ("+sql+") ct";
			
			Query countQuery = getSession().createSQLQuery(countSql);
			countQuery.setCacheable(true);
			if (params != null) {
				Iterator<?> list = params.entrySet().iterator();
				while (list.hasNext()) {
					Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
					countQuery.setParameter((String) entry.getKey(), entry.getValue());
				}
			}
			BigInteger records = (BigInteger) countQuery.uniqueResult();
			page.setRecords(records.longValue());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		
		List<?> list = query.list();
		page.setRows(list);
		return page;
	}
	/**
	 * 
	 *@category 据条件原生SQL分页查询（返回的list中对象为map）
	 *@author 邱磊
	 *@date 2015年7月10日 上午10:34:37
	 *@param sql
	 *@param params
	 *@param page
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getSqlPageMapList(String sql, Map<String, ?> params, Pagination page){
		Query query = getSession().createSQLQuery(sql);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				Object value = entry.getValue();
				if (value instanceof List) {
					query.setParameterList((String) entry.getKey(), (List<?>) value);
				} else {
					query.setParameter((String) entry.getKey(), entry.getValue());
				}
			}
		}
		if (page.getMaxResults() > 0) {
			String countSql = "select count(*) from ("+sql.toString()+") as count";
			Query countQuery = getSession().createSQLQuery(countSql);
			countQuery.setCacheable(true);
			if (params != null) {
				Iterator<?> list = params.entrySet().iterator();
				while (list.hasNext()) {
					Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
					Object value = entry.getValue();
					if (value instanceof List) {
						countQuery.setParameterList((String) entry.getKey(), (List<?>) value);
					} else {
						countQuery.setParameter((String) entry.getKey(), entry.getValue());
					}
				}
			}
			BigInteger records = (BigInteger) countQuery.uniqueResult();
			page.setRecords(records.longValue());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> list = query.list();
		page.setRows(list);
		return page;
	}
	
	/**
	 * 
	 *@category 据条件原生SQL分页查询（返回的list中对象为map）
	 *@author 邱磊
	 *@date 2015年7月10日 上午10:34:37
	 *@param sql
	 *@param params
	 *@param page
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getSqlPageMapList2(String sql, Map<String, ?> params, Pagination page){
		Query query = getSession().createSQLQuery(sql);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				Object value = entry.getValue();
				if (value instanceof List) {
					query.setParameterList((String) entry.getKey(), (List<?>) value);
				} else {
					query.setParameter((String) entry.getKey(), entry.getValue());
				}
			}
		}
		if (page.getMaxResults() > 0) {
			String countSql = "select count(*) from ("+sql.toString()+") as count";
			Query countQuery = getSession().createSQLQuery(countSql);
			countQuery.setCacheable(true);
			if (params != null) {
				Iterator<?> list = params.entrySet().iterator();
				while (list.hasNext()) {
					Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
					Object value = entry.getValue();
					if (value instanceof List) {
						countQuery.setParameterList((String) entry.getKey(), (List<?>) value);
					} else {
						countQuery.setParameter((String) entry.getKey(), entry.getValue());
					}
				}
			}
			BigInteger records = (BigInteger) countQuery.uniqueResult();
			page.setRecords(records.longValue());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> list = query.list();
		page.setRows(list);
		return page;
	}
	
	/**
	 * 
	 *@category ccl据条件原生SQL分页查询（返回的list中对象为map）
	 *@author 邱磊
	 *@date 2015年7月10日 上午10:34:37
	 *@param sql
	 *@param params
	 *@param page
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getPageMapList(String sql, Map<String, ?> params, Pagination page){
		Query query = getSession().createSQLQuery(sql);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				Object value = entry.getValue();
				if (value instanceof List) {
					query.setParameterList((String) entry.getKey(), (List<?>) value);
				} else {
					query.setParameter((String) entry.getKey(), entry.getValue());
				}
			}
		}
		if (page.getMaxResults() > 0) {
			int index = sql.toLowerCase().indexOf("from");
			String countSql = "select count(*) " + sql.substring(index);
			Query countQuery = getSession().createSQLQuery(countSql);
			countQuery.setCacheable(true);
			if (params != null) {
				Iterator<?> list = params.entrySet().iterator();
				while (list.hasNext()) {
					Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
					Object value = entry.getValue();
					if (value instanceof List) {
						countQuery.setParameterList((String) entry.getKey(), (List<?>) value);
					} else {
						countQuery.setParameter((String) entry.getKey(), entry.getValue());
					}
				}
			}
			BigInteger records = (BigInteger) countQuery.uniqueResult();
			page.setRecords(records.longValue());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> list = query.list();
		page.setRows(list);
		return page;
	}
	
	/**
	 *@category 根据条件原生SQL分页查询
	 *@author 邱磊
	 *@date 2015年6月15日 上午11:43:18
	 *@param sql
	 *@param params
	 *@param page
	 *@return
	 */
	public Pagination getSqlPageList(String sql, List<String> param, Pagination page){
		Query query = getSession().createSQLQuery(sql);
		if (param != null) {
			for (int i = 0; i < param.size(); i++) {
				query.setParameter(i, param.get(i));
			}
		}
		if (page.getMaxResults() > 0) {
			String countSql = "select count(*) from ("+sql+") ct";
			Query countQuery = getSession().createSQLQuery(countSql);
			countQuery.setCacheable(true);
			if (param != null) {
				for (int i = 0; i < param.size(); i++) {
					countQuery.setParameter(i, param.get(i));
				}
			}
			BigInteger records = (BigInteger) countQuery.uniqueResult();
			page.setRecords(records.longValue());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		List<?> list = query.list();
		page.setRows(list);
		return page;
	}
	
	@SuppressWarnings("unchecked")
	public Object getSqlUniqueResult(String sql,  Map<String, ?> params) {
		Query query = getSession().createSQLQuery(sql).setCacheable(true);
		
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		Object obj = query.uniqueResult();
		return obj;
	}
	@SuppressWarnings("unchecked")
	public boolean executeSql(String sql,Map<String, ?> params){
		Session session =getSession();
		Query query = session.createSQLQuery(sql);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				Object value = entry.getValue();
				if (value instanceof List) {
					query.setParameterList((String) entry.getKey(), (List<?>) value);
				} else {
					query.setParameter((String) entry.getKey(), entry.getValue());
				}
//				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		int flag = query.executeUpdate();
		session.flush();
		session.clear();
		if (flag > 0){
			return true;
		}
		return false;
	}
	@SuppressWarnings("unchecked")
	public List<?> getSqlMapList(String sql, Map<String, ?> params){
		Query query = getSession().createSQLQuery(sql).setCacheable(true);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				Object value = entry.getValue();
				if (value instanceof List) {
					query.setParameterList((String) entry.getKey(), (List<?>) value);
				} else {
					query.setParameter((String) entry.getKey(), entry.getValue());
				}
//				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> list = query.list();
		return list;
	}
	
	public Object getSqlMapUniqueResult(String sql, Map<String, ?> params){
		Query query = getSession().createSQLQuery(sql).setCacheable(true);
		if (params != null) {
			for( Entry<String, ?> entry : params.entrySet() ){
				query.setParameter( entry.getKey(), entry.getValue());
			}
//			Iterator<?> list = params.entrySet().iterator();
//			while (list.hasNext()) {
//				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
//				query.setParameter((String) entry.getKey(), entry.getValue());
//			}
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		Object obj = query.uniqueResult();
		return obj;
	}

	/**
	 * 
	 *@category 据条件原生SQL不分页查询（返回的list中对象为map）
	 *@author 韩守松
	 *@date 2015年12月3日 
	 *@param sql
	 *@param params
	 *@param page
	 *@return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getSqlNOPageMapList(String sql, Map<String, ?> params, Pagination page){
		Query query = getSession().createSQLQuery(sql);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				Object value = entry.getValue();
				if (value instanceof List) {
					query.setParameterList((String) entry.getKey(), (List<?>) value);
				} else {
					query.setParameter((String) entry.getKey(), entry.getValue());
				}
			}
		}
		if (page.getMaxResults() > 0) {
			int index = sql.toLowerCase().indexOf("from");
			String countSql = "select count(*) " + sql.substring(index);
			Query countQuery = getSession().createSQLQuery(countSql);
			countQuery.setCacheable(true);
			if (params != null) {
				Iterator<?> list = params.entrySet().iterator();
				while (list.hasNext()) {
					Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
					Object value = entry.getValue();
					if (value instanceof List) {
						countQuery.setParameterList((String) entry.getKey(), (List<?>) value);
					} else {
						countQuery.setParameter((String) entry.getKey(), entry.getValue());
					}
				}
			}
			BigInteger records = (BigInteger) countQuery.uniqueResult();
			page.setRecords(records.longValue());
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> list = query.list();
		page.setRows(list);
		return page;
	}
	
	/**
	 * 
	 * @category ccl据条件原生SQL分页查询（返回的list中对象为map）
	 * @author 韩守松
	 * @date   2016年2月18日
	 * @param  @param sql
	 * @param  @param params
	 * @param  @param page
	 * @param  @return
	 */
	
	@SuppressWarnings("unchecked")
	public Pagination getPageSelectMapList(String sql, Map<String, ?> params, Pagination page){
		Query query = getSession().createSQLQuery(sql);
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				Object value = entry.getValue();
				if (value instanceof List) {
					query.setParameterList((String) entry.getKey(), (List<?>) value);
				} else {
					query.setParameter((String) entry.getKey(), entry.getValue());
				}
			}
		}
		if (page.getMaxResults() > 0) {
			int index = sql.toLowerCase().indexOf("from");
			String countSql = "select count(*) from ( select count(*),a.order_status,a.qualityspec_name,a.qualityspec_name_spel,a.drug_code,a.prod_compname,a.prod_compname_spel " + sql.substring(index)+" ) a";
			Query countQuery = getSession().createSQLQuery(countSql);
			countQuery.setCacheable(true);
			if (params != null) {
				Iterator<?> list = params.entrySet().iterator();
				while (list.hasNext()) {
					Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
					Object value = entry.getValue();
					if (value instanceof List) {
						countQuery.setParameterList((String) entry.getKey(), (List<?>) value);
					} else {
						countQuery.setParameter((String) entry.getKey(), entry.getValue());
					}
				}
			}
			BigInteger records = (BigInteger) countQuery.uniqueResult();
			page.setRecords(records.longValue());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> list = query.list();
		page.setRows(list);
		return page;
	}
}